/**
 * Copyright(c) by JNet Smartware Group.
 * All rights reserved. Not distribution or redistribution without
 * any permission of JNet Smartware Group.
 * 
 * @author thuc
 * 2010/03/15
 */
package vn.smartware.co.server.dao.initialize;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import vn.smartware.co.framework.bean.LoginDTO;
import vn.smartware.co.framework.exception.ServerException;
import vn.smartware.co.framework.form.LoginForm;
import vn.smartware.co.server.sql.factory.AbstractLoginSQLManager;

/**
 * @author thuctt
 *
 */
public class LoginSQLManager extends AbstractLoginSQLManager {

	private static Log log = LogFactory.getLog(LoginSQLManager.class);
	
	/**
	 * check login name exist
	 * 
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void makeChkUserNameExistSQL(LoginForm form, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT COUNT(*) AS CNT ")
		
		.append(" FROM MST_USER ")
		
		.append(" WHERE USER_NAME LIKE BINARY ? ")
		.append(" AND DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, form.getUsername());
			log.info("USER_NAME = " + form.getUsername());
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * check account exist
	 * 
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void makeGetLoginInfoSQL(LoginForm form, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT USER_ID, PASSWORD ")
		
		.append(" FROM MST_USER ")
		
		.append(" WHERE USER_NAME LIKE BINARY ? ")
		.append(" AND DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, form.getUsername());
			log.info("USER_NAME = " + form.getUsername());
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * makeChkUserLoginSQL
	 * 
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void makeGetUserInfoSQL(LoginForm form, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" USR.USER_ID, USR.FULL_NAME, USR.INIT_PRG_ID, ")
		.append(" IFNULL(LOGIN.LOGIN_TRY_NUMBER,-1) AS LOGIN_TRY_NUMBER, ")
		.append(" LOGIN.NO ")
		
		.append(" FROM MST_USER USR ")
		
		.append(" LEFT OUTER JOIN LOGIN ")
		.append(" ON LOGIN.USER_NAME = USR.USER_NAME AND LOGIN.DELETE_DATE IS NULL ")
		.append(" AND LOGIN.NO = ? ")
		
		.append(" WHERE USR.USER_ID = ? ")
		.append(" AND USR.DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, form.getLoginIndex());
			log.info("LOGIN.NO = " + form.getLoginIndex());
			
			ps.setInt(i++, form.getUserCd());
			log.info("USER_ID = " + form.getUserCd());
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * increase number of trying login
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void makeIncreaseTryNumberSQL(LoginForm form, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE LOGIN ")
		.append(" SET LOGIN_TRY_NUMBER = ?, ")
		.append(" UPDATE_USER_ID = 99999, ")
		.append(" UPDATE_DATE = SYSDATE() ")
		.append(" WHERE USER_NAME LIKE BINARY ? ")
		.append(" AND DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, form.getLoginTryNumber());
			log.info("LOGIN_TRY_NUMBER = " + form.getLoginTryNumber());
			
			ps.setString(i++, form.getUsername());
			log.info("USER_NAME = " + form.getUsername());
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * count login number SQL
	 * 
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void makeCountLoginTryNumberSQL(LoginForm form, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT IFNULL(LOGIN_TRY_NUMBER, -1) AS CNT ")
		
		.append(" FROM LOGIN ")
		
		.append(" WHERE NO = ? ")
		.append(" AND DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, form.getLoginIndex());
			log.info("NO = " + form.getLoginIndex());
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * create first login time
	 * 
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void makecreateFirstLoginTimeSQL(LoginForm form, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" INSERT INTO LOGIN ( ")
		.append(" USER_NAME, LOGIN_DATE, LOGIN_TRY_NUMBER, INSERT_USER_ID, INSERT_DATE, UPDATE_USER_ID, UPDATE_DATE ")
		.append(" ) VALUES (?, SYSDATE(), 1, 99999, SYSDATE(), 99999, SYSDATE()) ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, form.getUsername());
			log.info("USER_NAME = " + form.getUsername());
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * reset trying number to 0
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void makeUpdateLoginSucessfulStatusSQL(LoginForm form, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE LOGIN SET ")
		.append(" LOGIN_TRY_NUMBER = 0, ")
		.append(" LOGIN_DATE = SYSDATE(), ")
		.append(" UPDATE_USER_ID = 99999, ")
		.append(" UPDATE_DATE = SYSDATE() ")
		.append(" WHERE USER_NAME LIKE BINARY ? ")
		.append(" AND DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, form.getUsername());
			log.info("USER_NAME = " + form.getUsername());
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("LoginSQLManager makeUpdateLoginSucessfulStatusSQL SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * lock account
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void makeLockAccountSQL(LoginForm form, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE MST_USER SET ")
		.append(" LOCK_OUT_DATE = CURDATE(), ")
		.append(" UPDATE_USER_ID = 99999, ")
		.append(" UPDATE_DATE = SYSDATE() ")
		.append(" WHERE USER_NAME LIKE BINARY ? ")
		.append(" AND DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, form.getUsername());
			log.info("USER_NAME = " + form.getUsername());
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * check account locked
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void makeIsAccountLockedSQL(LoginForm form, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT LOCK_OUT_DATE ")
		
		.append(" FROM MST_USER ")
		
		.append(" WHERE USER_NAME LIKE BINARY ? ")
		.append(" AND DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, form.getUsername());
			log.info("USER_NAME = " + form.getUsername());
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * reset login status
	 * @param form
	 * @param conn
	 * @throws ServerException
	 */
	public void makeResetLoginStatusSQL(LoginForm form, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE LOGIN SET ")
		.append(" LOGIN_TRY_NUMBER = 0, ")
		.append(" LOGIN_DATE = NULL, ")
		.append(" UPDATE_USER_ID = 99999, ")
		.append(" UPDATE_DATE = SYSDATE() ")
		.append(" WHERE USER_NAME = ? ")
		.append(" AND DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setString(i++, form.getUsername());
			log.info("USER_NAME = " + form.getUsername());
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("LoginSQLManager makeResetLoginStatusSQL SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeGetMaxLoginNoSQL
	 * 
	 * @param conn
	 * @throws ServerException
	 */
	public void makeGetMaxLoginNoSQL(Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT MAX(NO) AS MAX_NO ")
		
		.append(" FROM LOGIN ")
		
		.append(" WHERE DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * reset trying number to 0
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	public void makeSetLogoutTimerSQL(LoginDTO user, Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" UPDATE LOGIN SET ")
		.append(" LOGOUT_DATE = SYSDATE(), ")
		.append(" UPDATE_USER_ID = 99999, ")
		.append(" UPDATE_DATE = SYSDATE() ")
		.append(" WHERE NO = ? ")
		.append(" AND DELETE_DATE IS NULL ")
		;
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			ps.setInt(i++, user.getLoginNo());
			log.info("NO = " + user.getLoginNo());
			
			precompilePreparedStatement(ps);
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
}
